package edu.hawaii.myisern.model;

import java.util.ArrayList;
import java.util.List;

/**
 * Applies MyIsern specific data types to the database interface.
 * 
 * @author Randy Cox
 * 
 */
public class DatabaseCollaborations {

  private final String className = "DatabaseCollaborations";

  private Database db;

  /** Collaboration table name for production. */
  public final String COLLABORATIONS_TABLE = "COLLABORATIONS";

  /**
   * Main collaboration table variables.
   */

  /** Collaboration table name to use. */
  private String collaborationsTable = COLLABORATIONS_TABLE;

  /** Collaboration create table command. */
  private String collaborationCreateTableSql;

  /** Collaboration drop table command. */
  private String collaborationDropTableSql;

  /** Collaboration select command. */
  private String collaborationSelectSql;

  /** Collaboration insert command. */
  private String collaborationInsertSql;

  /** Collaboration update command. */
  private String collaborationUpdateSql;

  /** Collaboration delete command. */
  private String collaborationDeleteSql;

  /** Collaboration select count command. */
  private String collaborationCountSql;

  /** Collaboration clear command. */
  private String collaborationClearSql;

  /** Collaboration list command. */
  private String collaborationListSql;

  /**
   * Collaborating organizations table variables. /
   * 
   * /** Collaborating organizations table name for production.
   */
  public final String ORGANIZATIONS_TABLE = "COLLABORATINGORGANIZATIONS";

  /** Collaborating organizations table name to use. */
  private String organizationsTable = ORGANIZATIONS_TABLE;

  /** Collaborating organizations create table command. */
  private String organizationsCreateTableSql;

  /** Collaborating organizations drop table command. */
  private String organizationsDropTableSql;

  /** Collaborating organizations select command. */
  private String organizationsSelectSql;

  /** Collaborating organizations update command. */
  private String organizationsInsertSql;

  /** Collaborating organizations select update command. */
  private String organizationsDeleteSql;

  /** Collaborating organizations clear command. */
  private String organizationsClearSql;

  /**
   * Collaborating years table variables. /
   * 
   * /** Collaborating years table name for production.
   */
  public final String YEARS_TABLE = "COLLABORATINGYEARS";

  /** Collaborating years table name to use. */
  private String yearsTable = YEARS_TABLE;

  /** Collaborating years create table command. */
  private String yearsCreateTableSql;

  /** Collaborating years drop table command. */
  private String yearsDropTableSql;

  /** Collaborating years select command. */
  private String yearsSelectSql;

  /** Collaborating years update command. */
  private String yearsInsertSql;

  /** Collaborating years select update command. */
  private String yearsDeleteSql;

  /** Collaborating years clear command. */
  private String yearsClearSql;

  /**
   * Collaborating types table variables. /
   * 
   * /** Collaborating types table name for production.
   */
  public final String TYPES_TABLE = "COLLABORATINGTYPES";

  /** Collaborating types table name to use. */
  private String typesTable = TYPES_TABLE;

  /** Collaborating types create table command. */
  private String typesCreateTableSql;

  /** Collaborating types drop table command. */
  private String typesDropTableSql;

  /** Collaborating types select command. */
  private String typesSelectSql;

  /** Collaborating types update command. */
  private String typesInsertSql;

  /** Collaborating types select update command. */
  private String typesDeleteSql;

  /** Collaborating types clear command. */
  private String typesClearSql;

  /**
   * Collaborating outcomes table variables. /
   * 
   * /** Collaborating outcomes table name for production.
   */
  public final String OUTCOMES_TABLE = "COLLABORATINGOUTCOMES";

  /** Collaborating outcomes table name to use. */
  private String outcomesTable = OUTCOMES_TABLE;

  /** Collaborating outcomes create table command. */
  private String outcomesCreateTableSql;

  /** Collaborating outcomes drop table command. */
  private String outcomesDropTableSql;

  /** Collaborating outcomes select command. */
  private String outcomesSelectSql;

  /** Collaborating outcomes update command. */
  private String outcomesInsertSql;

  /** Collaborating outcomes select update command. */
  private String outcomesDeleteSql;

  /** Collaborating outcomes clear command. */
  private String outcomesClearSql;

  /**
   * Constructor, initializes driver, if testing, database to test database.
   * 
   */
  public DatabaseCollaborations() {
    String methodName = ".DatabaseCollaborations(): ";
    Log.enter(className + methodName + this.collaborationsTable);
    Log.enter(className + methodName + this.organizationsTable);
    Log.enter(className + methodName + this.yearsTable);
    Log.enter(className + methodName + this.typesTable);
    Log.enter(className + methodName + this.outcomesTable);
    db = new Database();
    setSqlStrings();
  }

  /**
   * Sets the sql string commands based on current table names.
   */
  public final void setSqlStrings() {

    /** Short cuts for sql where statement. */
    String where = " WHERE collaboration_id = ?";

    /** Short cuts for sql value statement. */
    String values = " VALUES (?, ?)";

    /**
     * Collaboration table commands.
     */

    // Collaboration create table command.
    this.collaborationCreateTableSql = db.CREATE_TABLE + this.collaborationsTable + " ("
        + "id VARCHAR(255) PRIMARY KEY, " + "name VARCHAR(255), description VARCHAR(255))";

    // Collaboration drop table command.
    this.collaborationDropTableSql = db.DROP_TABLE + this.collaborationsTable;

    // Collaboration select command.
    this.collaborationSelectSql = db.SELECT_FROM + this.collaborationsTable + " WHERE id = ?";

    // Collaboration insert command.
    this.collaborationInsertSql = db.INSERT_INTO + this.collaborationsTable
        + " (id, name, description) VALUES (?, ?, ?)";

    // Collaboration select update command.
    this.collaborationUpdateSql = db.UPDATE + this.collaborationsTable
        + " SET name = ?, description = ? WHERE id = ?";

    // Collaboration delete command.
    this.collaborationDeleteSql = db.DELETE_FROM + this.collaborationsTable + " WHERE id = ?";

    // Collaboration count command.
    this.collaborationCountSql = db.SELECT_COUNT + this.collaborationsTable;

    // Collaboration delete command.
    this.collaborationClearSql = db.DELETE_FROM + this.collaborationsTable;

    // Collaboration list command.
    this.collaborationListSql = db.SELECT_FROM + this.collaborationsTable;

    /**
     * Collaboration organization table commands.
     */

    // Collaborating organization create table command.
    this.organizationsCreateTableSql = db.CREATE_TABLE + this.organizationsTable + " ("
        + "collaboration_id VARCHAR(255), researcher_name VARCHAR(255))";

    // Collaborating organization drop table command.
    this.organizationsDropTableSql = db.DROP_TABLE + this.organizationsTable;

    // Collaborating organization select command.
    this.organizationsSelectSql = "SELECT researcher_name FROM " + this.organizationsTable + where;

    // Collaborating organization insert command.
    this.organizationsInsertSql = db.INSERT_INTO + this.organizationsTable
        + " (collaboration_id, researcher_name)" + values;

    // Collaborating organization delete command.
    this.organizationsDeleteSql = db.DELETE_FROM + this.organizationsTable + where;

    // Collaborating organization clear command.
    this.organizationsClearSql = db.DELETE_FROM + this.organizationsTable;

    /**
     * Collaboration years table commands.
     */

    // Collaborating years create table command.
    this.yearsCreateTableSql = db.CREATE_TABLE + this.yearsTable + " ("
        + "collaboration_id VARCHAR(255), collaboration_year VARCHAR(255))";

    // Collaborating years drop table command.
    this.yearsDropTableSql = db.DROP_TABLE + this.yearsTable;

    // Collaborating years select command.
    this.yearsSelectSql = "SELECT collaboration_year FROM " + this.yearsTable + where;

    // Collaborating years insert command.
    this.yearsInsertSql = db.INSERT_INTO + this.yearsTable
        + " (collaboration_id, collaboration_year)" + values;

    // Collaborating years delete command.
    this.yearsDeleteSql = db.DELETE_FROM + this.yearsTable + where;

    // Collaborating years clear command.
    this.yearsClearSql = db.DELETE_FROM + this.yearsTable;

    /**
     * Collaboration types table commands.
     */

    // Collaborating types create table command.
    this.typesCreateTableSql = db.CREATE_TABLE + this.typesTable + " ("
        + "collaboration_id VARCHAR(255), collaboration_type VARCHAR(255))";

    // Collaborating types drop table command.
    this.typesDropTableSql = db.DROP_TABLE + this.typesTable;

    // Collaborating types select command.
    this.typesSelectSql = "SELECT collaboration_type FROM " + this.typesTable + where;

    // Collaborating types insert command.
    this.typesInsertSql = db.INSERT_INTO + this.typesTable
        + " (collaboration_id, collaboration_type)" + values;

    // Collaborating types delete command.
    this.typesDeleteSql = db.DELETE_FROM + this.typesTable + where;

    // Collaborating types clear command.
    this.typesClearSql = db.DELETE_FROM + this.typesTable;

    /**
     * Collaboration outcomes table commands.
     */

    // Collaborating outcomes create table command.
    this.outcomesCreateTableSql = db.CREATE_TABLE + this.outcomesTable + " ("
        + "collaboration_id VARCHAR(255), collaboration_outcome VARCHAR(255))";

    // Collaborating outcomes drop table command.
    this.outcomesDropTableSql = db.DROP_TABLE + this.outcomesTable;

    // Collaborating outcomes select command.
    this.outcomesSelectSql = "SELECT collaboration_outcome FROM " + this.outcomesTable + where;

    // Collaborating outcomes insert command.
    this.outcomesInsertSql = db.INSERT_INTO + this.outcomesTable
        + " (collaboration_id, collaboration_outcome)" + values;

    // Collaborating outcomes delete command.
    this.outcomesDeleteSql = db.DELETE_FROM + this.outcomesTable + where;

    // Collaborating outcomes clear command.
    this.outcomesClearSql = db.DELETE_FROM + this.outcomesTable;

  }

  /**
   * Open and connect to database.
   */
  private void connect() {
    db.connect();
  }

  /**
   * Check and create all tables.
   */
  private void open() {
    createTables();
  }

  /**
   * Check if collaboration table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableCollaborations() {
    connect();
    Boolean found = db.hasTable(this.collaborationsTable);
    return found;
  }

  /**
   * Check if collaborating organizations table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableOrganizations() {
    connect();
    Boolean found = db.hasTable(this.organizationsTable);
    return found;
  }

  /**
   * Check if years table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableYears() {
    connect();
    Boolean found = db.hasTable(this.yearsTable);
    return found;
  }

  /**
   * Check if types table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableTypes() {
    connect();
    Boolean found = db.hasTable(this.typesTable);
    return found;
  }

  /**
   * Check if outcome table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableOutcomes() {
    connect();
    Boolean found = db.hasTable(this.outcomesTable);
    return found;
  }

  /**
   * Checks if collaboration table exits, if not creates it.
   */
  public void createTables() {
    String methodName = ".createTables(): ";
    connect();

    // create collaboration table.
    if (hasTableCollaborations()) {
      String msg = className + methodName + this.collaborationsTable
          + " collaboration table found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.collaborationCreateTableSql);
      String msg = className + methodName + this.collaborationsTable
          + " collaboration table not found.  Table created.";
      Log.enter(msg);
    }

    // create organizations table.
    if (hasTableOrganizations()) {
      String msg = className + methodName + this.organizationsTable + " organization table found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.organizationsCreateTableSql);
      String msg = className + methodName + this.organizationsCreateTableSql
          + " organization table not found.  Table created.";
      Log.enter(msg);
    }

    // create years table.
    if (hasTableYears()) {
      String msg = className + methodName + this.yearsTable + " year table found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.yearsCreateTableSql);
      String msg = className + methodName + this.yearsCreateTableSql
          + " years table not found.  Table created.";
      Log.enter(msg);
    }

    // create typess table.
    if (hasTableTypes()) {
      String msg = className + methodName + this.typesTable + " types table found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.typesCreateTableSql);
      String msg = className + methodName + this.typesCreateTableSql
          + " types table not found.  Table created.";
      Log.enter(msg);
    }

    // create outcomes table.
    if (hasTableOutcomes()) {
      String msg = className + methodName + this.outcomesTable + " outcomes table found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.outcomesCreateTableSql);
      String msg = className + methodName + this.outcomesCreateTableSql
          + " outcomes table not found.  Table created.";
      Log.enter(msg);
    }
  }

  /**
   * Checks database to see if collaboration with id already exists.
   * 
   * @param orgId of collaboration record.
   * @return boolean if record exists.
   */
  public Boolean hasCollaboration(String orgId) {
    String methodName = ".hasCollaboration(): ";
    connect();
    String id = Id.build(orgId);
    Boolean found = (get(id) != null);
    Log.enter(className + methodName + found.toString());
    return found;
  }

  /**
   * Retrieves collaboration from database.
   * 
   * @param orgId of collaboration to retrieve.
   * @return collaboration if found, null if not found.
   */
  public MyCollaboration get(String orgId) {
    String methodName = ".get(): ";
    connect();
    open();

    // Make sure id is in correct format.
    String id = Id.build(orgId);

    // Get the collaboration record
    List<List<String>> results = db.query(this.collaborationSelectSql, id);
    Boolean found = false;
    if (results.size() == 1) {
      String resultId = Id.build(results.get(0).get(0));
      if (resultId.equalsIgnoreCase(id)) {
        found = true;
      }
    }
    Log.enter(className + methodName + "collaboration found = " + found.toString());
    MyCollaboration collaboration = null;
    if (found) {
      List<String> row = results.get(0);
      collaboration = new MyCollaboration();
      collaboration.setName(row.get(1));
      collaboration.setDescription(row.get(2));
    }

    // Get any organizations related to this collaboration.
    results = db.query(this.organizationsSelectSql, id);
    for (List<String> row : results) {
      collaboration.addOrganization(row.get(0));
      Log.enter(className + methodName + "organization found = " + row.get(0));
    }

    // Get years related to this collaboration.
    results = db.query(this.yearsSelectSql, id);
    for (List<String> row : results) {
      collaboration.addYear(row.get(0));
      Log.enter(className + methodName + "year found = " + row.get(0));
    }

    // Get types related to this collaboration.
    results = db.query(this.typesSelectSql, id);
    for (List<String> row : results) {
      collaboration.addType(row.get(0));
      Log.enter(className + methodName + "type found = " + row.get(0));
    }

    // Get outcomes related to this collaboration.
    results = db.query(this.outcomesSelectSql, id);
    for (List<String> row : results) {
      collaboration.addOutcome(row.get(0));
      Log.enter(className + methodName + "outcome found = " + row.get(0));
    }

    Log.enter(className + methodName + " dumping collaboration:");
    if (collaboration == null) {
      Log.enter("null");
    }
    else {
      Log.enter("id =" + collaboration.getId() + ", name =" + collaboration.getName() + ", desc ="
          + collaboration.getDescription());
    }
    return collaboration;
  }

  /**
   * Insert or update collaboration record into table.
   * 
   * @param collaboration record to save.
   * @return count of records updated.
   */
  public Integer save(MyCollaboration collaboration) {
    String methodName = ".save(): ";
    assert (collaboration != null);
    assert (collaboration.validate());
    connect();
    open();

    // If collaboration is already in database, update it.
    Integer updateCount = 0;
    if (hasCollaboration(collaboration.getId())) {
      updateCount = db.update(this.collaborationUpdateSql, collaboration.getName(), collaboration
          .getDescription(), collaboration.getId());
      Log.enter(className + methodName + updateCount.toString() + " collaborations updated.");
    }

    // If collaboration not in database, insert it.
    else {
      updateCount = db.update(this.collaborationInsertSql, collaboration.getId(), collaboration
          .getName(), collaboration.getDescription());
      Log.enter(className + methodName + updateCount.toString() + " collaborations inserted.");
    }

    // Deleted associated affiliated researchers.
    Integer count = db.update(this.organizationsDeleteSql, collaboration.getId());
    Log.enter(className + methodName + count.toString() + " affiliated res deleted.");

    // Deleted associated affiliated researchers.
    count = db.update(this.yearsDeleteSql, collaboration.getId());
    Log.enter(className + methodName + count.toString() + " years deleted.");

    // Deleted associated affiliated researchers.
    count = db.update(this.typesDeleteSql, collaboration.getId());
    Log.enter(className + methodName + count.toString() + " types deleted.");

    // Deleted associated affiliated researchers.
    count = db.update(this.outcomesDeleteSql, collaboration.getId());
    Log.enter(className + methodName + count.toString() + " outcomes deleted.");

    // Insert associated affiliated researchers.
    for (String name : collaboration.getOrganizations()) {
      db.update(this.organizationsInsertSql, collaboration.getId(), name);
      Log.enter(className + methodName + name + " organization inserted for collaboration:"
          + collaboration.getId());
    }

    // Insert associated years.
    for (String year : collaboration.getYears()) {
      db.update(this.yearsInsertSql, collaboration.getId(), year);
      Log.enter(className + methodName + year + "year inserted for collaboration:"
          + collaboration.getId());
    }

    // Insert associated types.
    for (String type : collaboration.getTypes()) {
      db.update(this.typesInsertSql, collaboration.getId(), type);
      Log.enter(className + methodName + type + " type inserted for collaboration:"
          + collaboration.getId());
    }

    // Insert associated outcomes.
    for (String outcome : collaboration.getOutcomes()) {
      db.update(this.outcomesInsertSql, collaboration.getId(), outcome);
      Log.enter(className + methodName + outcome + "outcome inserted for collaboration:"
          + collaboration.getId());
    }

    return updateCount;
  }

  /**
   * Delete collaboration information from collaboration and sub tables.
   * 
   * @param id collaboration record to delete.
   * @return count of records deleted.
   */
  public Integer delete(String id) {
    String methodName = ".delete(): ";
    assert (id != null);
    connect();
    open();

    // Make sure id is in correct format.
    String org_id = Id.build(id);

    // If collaboration is already in database, delete it.
    Integer deleteCount = 0;
    if (hasCollaboration(org_id)) {
      deleteCount = db.update(this.collaborationDeleteSql, org_id);
      Log.enter(className + "." + methodName + deleteCount.toString() + " coll deleted.");
    }
    else {
      Log.enter(className + "." + methodName + "record not found " + org_id);
    }

    // Delete associated affiliated researchers.
    Integer count = db.update(this.organizationsDeleteSql, org_id);
    Log.enter(className + "." + methodName + "aff orgs " + count.toString() + " af orgs deleted.");

    // Delete associated years.
    count = db.update(this.yearsDeleteSql, org_id);
    Log.enter(className + "." + methodName + "years " + count.toString() + " years deleted.");

    // Delete associated types.
    count = db.update(this.typesDeleteSql, org_id);
    Log.enter(className + "." + methodName + "types " + count.toString() + " types deleted.");

    // Delete associated outcomes.
    count = db.update(this.outcomesDeleteSql, org_id);
    Log.enter(className + "." + methodName + "outcomes " + count.toString() + "outcomes deleted.");

    return deleteCount;
  }

  /**
   * Drop collaboration table.
   */
  public void dropTables() {
    String methodName = ".dropTables(): ";
    connect();

    // Drop collaboration table if it exists.
    if (db.hasTable(this.collaborationsTable)) {
      db.execute(this.collaborationDropTableSql);
      String msg = className + methodName + this.collaborationsTable
          + " coll found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.collaborationsTable
          + " not found.  Doing nothing.";
      Log.enter(msg);
    }

    // Drop affiliated researcher table if it exists.
    if (db.hasTable(this.organizationsTable)) {
      db.execute(this.organizationsDropTableSql);
      String msg = className + methodName + this.organizationsTable
          + " af orgs found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.organizationsTable
          + " af orgs not found.  Doing nothing.";
      Log.enter(msg);
    }

    // Drop years table if it exists.
    if (db.hasTable(this.yearsTable)) {
      db.execute(this.yearsDropTableSql);
      String msg = className + methodName + this.yearsTable + " years found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.yearsTable + " years not found.  Doing nothing.";
      Log.enter(msg);
    }

    // Drop type table if it exists.
    if (db.hasTable(this.typesTable)) {
      db.execute(this.typesDropTableSql);
      String msg = className + methodName + this.typesTable + " types found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.typesTable + " types not found.  Doing nothing.";
      Log.enter(msg);
    }
    // Drop outcome table if it exists.
    if (db.hasTable(this.outcomesTable)) {
      db.execute(this.outcomesDropTableSql);
      String msg = className + methodName + this.outcomesTable + " outcomes found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.outcomesTable
          + " outcomes not found.  Doing nothing.";
      Log.enter(msg);
    }
  }

  /**
   * Clear all collaboration information from collaboration and sub tables.
   */
  public void clear() {
    String methodName = ".clear(): ";
    connect();
    open();

    // If collaboration is already in database, delete it.
    Integer count = db.update(this.collaborationClearSql);
    Log.enter(className + methodName + count.toString() + " collaborations cleared.");

    // Delete associated affiliated researchers.
    count = db.update(this.organizationsClearSql);
    Log.enter(className + methodName + count.toString() + " organizations cleared.");

    // Delete years.
    count = db.update(this.yearsClearSql);
    Log.enter(className + methodName + count.toString() + " years cleared.");

    // Delete types.
    count = db.update(this.typesClearSql);
    Log.enter(className + methodName + count.toString() + " types cleared.");

    // Delete years.
    count = db.update(this.outcomesClearSql);
    Log.enter(className + methodName + count.toString() + " outcomes cleared.");
  }

  /**
   * Count of collaborations in table.
   * 
   * @return count of records.
   */
  public Integer count() {
    String methodName = ".count(): ";
    connect();
    open();

    // Get count of the collaboration records
    List<List<String>> results = db.query(this.collaborationCountSql);
    Integer count = Integer.parseInt(results.get(0).get(0));
    Log.enter(className + methodName + count.toString());
    return count;
  }

  /**
   * List of researchers in table.
   * 
   * @return List of collaborations.
   */
  public List<MyCollaboration> list() {
    connect();
    open();

    List<MyCollaboration> list = new ArrayList<MyCollaboration>();
    List<List<String>> results = db.query(this.collaborationListSql);
    for (List<String> row : results) {
      Id id = new Id(row.get(1));
      MyCollaboration item = this.get(id.toString());
      item.setDescription(item.getDescription().trim());
      list.add(item);
    }
    return list;

  }

}
